package com.elite.ngs.controllers;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.*;
import com.elite.jss.JssException;
import com.elite.jss.JssTools;
import com.elite.jss.JssUtils;
import com.elite.ngs.beans.Admin;
import com.elite.ngs.beans.ProjInfo;
import com.elite.ngs.beans.Staff;
import com.elite.ngs.beans.dataExport.DataExportRequest;
import com.elite.ngs.beans.dataExport.DbSqlInfo;
import com.elite.ngs.beans.poi.Request;
import com.elite.ngs.beans.poi.Runner;
import com.elite.ngs.internal.Constant;
import com.elite.ngs.internal.DataService;
import com.elite.ngs.services.TableDataExportService;
import com.elite.ngs.utils.DSBean;
import com.elite.ngs.utils.StaffUtils;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.wisdge.commons.filestorage.FileStorage;
import com.wisdge.commons.sms.SmsResponse;
import com.wisdge.dataservice.Result;
import com.wisdge.dataservice.utils.JSonUtils;
import com.wisdge.utils.DateUtils;
import com.wisdge.utils.RandomUtils;
import com.wisdge.utils.StringUtils;
import com.wisdge.web.springframework.WebUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;
import org.json.JSONObject;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Stream;

@Slf4j
@Controller
@RequestMapping("/reporter")
public class ReporterController extends BasicController {

    @Resource
    private FileStorage fileStorage;
    @Resource
    TableDataExportService tableDataExportService;

    private static final String FILE_SUFFIX = ".xlsx";

    @RequestMapping(value = "/export_")
    @ResponseBody
    public Result export() {
        try {
            String payload = WebUtils.getRequestPayload(request);
            if (StringUtils.isEmpty(payload)) {
                out("无效的PAYLOAD数据");
            }
            Map<String, Object> map = JSonUtils.read(payload, Map.class);
            Result result = reportAccreditAuth(map);
            if (result.getCode() > 0) {
                String token = (String) map.get(Constant.STRING_TOKEN);
                int digest = (int) map.get("digest");
                String dbPool = (String) map.get(Constant.STRING_DBPOOL);
                String id = (String) map.get("id");
                map.remove("digest");
                map.remove(Constant.STRING_DBPOOL);
                payload = JSonUtils.parse(map);
                Request runRequest = new ObjectMapper().readValue(payload, Request.class);
                Runner runner = new Runner(runRequest);
                String guid = UUID.randomUUID().toString();
                sqlExecute(token, digest, dbPool, "newReport.dw.insert", guid, id, 1);
                String sheetName = i18n("sheet.first");
                return fileStorage.saveFile(FileStorage.DEFAULT_STORAGE, "reporter", "automatic.xlsx", RandomUtils.getGUID() + ".xlsx", runner.start(sheetName));
            } else {
                return result;
            }
        } catch (Exception e) {
            log.error("Report export error", e);
            return new Result(Result.ERROR, e.getMessage());
        }
    }

    @RequestMapping(value = "/download_")
    @ResponseBody
    public Result download() {
        try {
            String token = WebUtils.getString(request, Constant.STRING_TOKEN);
            int digest = WebUtils.getInteger(request, "digest");
            String dbPool = WebUtils.getString(request, Constant.STRING_DBPOOL);
            String id = WebUtils.getString(request, "id");
            String guid = UUID.randomUUID().toString();
            String path = WebUtils.getString(request, "path");
            String name = WebUtils.getString(request, "name");
            log.debug("Report download, path: {}  name: {}", path, name);
            if (StringUtils.isEmpty(name)) {
                name = FilenameUtils.getName(path);
            }
            sqlExecute(token, digest, dbPool, "newReport.dw.insert", guid, id, 2);
            byte[] data = fileStorage.retrieveFile(FSController.DEFAULT_FS_KEY, path);
            out(data, name, true);
            return null;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return new Result(Result.ERROR, e.getMessage());
        }
    }

    @RequestMapping(value = "/sendSms_")
    @ResponseBody
    public Result sendSms() {
        try {
            HttpSession httpSession = request.getSession();
            JSONObject requestParams = new JSONObject(WebUtils.getRequestPayload(request));
            String uid = requestParams.optString("uid");
            String ecsid = requestParams.optString(Constant.STRING_ECSID);
            String reportName = requestParams.optString("reportName");
            String mobile;
            DataService dataService = (DataService) httpSession.getAttribute(Constant.SESSION_DS);
            Staff staff = (Staff) httpSession.getAttribute(Constant.STRING_STAFF);
            ProjInfo project = StaffUtils.getCurProject(staff);
            Result result = sqlQuery(dataService, project.getToken(), project.getOrdIndex(), "", "report.staff.findAccreditMobile", 1, 0, staff.getId());
            if (result.getCode() > 0) {
                DSBean dsb = getExecuteData(result);
                List<List<Object>> rows = dsb.getFields();
                if (rows.size() > 0) {
                    mobile = (String) rows.get(0).get(0);
                    if (StringUtils.isEmpty(mobile)) {
                        return new Result(-3, "没有导出权限");
                    }
                } else {
                    return new Result(-3, "没有导出权限");
                }
            } else {
                return new Result(Result.ERROR, result.getMessage());
            }
            return captchaService.sendSmsCaptcha(mobile, uid, ecsid);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return new Result(Result.ERROR, e.getMessage());
        }
    }

    protected int sendCaptcha(String mobile, LinkedHashMap<String, Object> params) throws Exception {
        String[] mobiles = new String[]{mobile};
        SmsResponse smsResponse = smsService.send(mobiles, params, Constant.SESSION_CAPTCHA);
        return smsResponse.getSucceed();
    }

    @RequestMapping(value = "/parseSelectItems")
    @ResponseBody
    public Result parseSelectItems() throws JssException {
        HttpSession httpSession = request.getSession();
        Admin admin = (Admin) httpSession.getAttribute(Constant.SESSION_ADMIN);
        DataSource dataSource = JssTools.getJdbc(admin.getToken());
        String dbType = JssUtils.fetchDBType(dataSource);
        if (dbType.toLowerCase().contains("dm")) {
            dbType = "DMDB";
        } else if (dbType.toLowerCase().contains("microsoft") || dbType.toLowerCase().contains("mssql")) {
            dbType = "SQL";
        } else if (dbType.toLowerCase().contains("postgresql")) {
            dbType = "PGSQL";
        }
        String dbKey = "#" + dbType + "#[";

        String sql = WebUtils.getRequestPayload(request);
        sql = com.elite.ngs.utils.StringUtils.base64Decode(sql);
        sql = com.elite.ngs.utils.StringUtils.decodeURI(sql);
        log.debug("Parse sql: {}", sql);
        // 截取对应数据库类型的SQL语句
        int index = sql.indexOf(dbKey.toUpperCase());
        int end = sql.indexOf("#]#", index + 5);
        if (index > -1) {
            if (end > -1) {
                sql = sql.substring(index + dbKey.length(), end);
            }
        } else {
            if (end > -1) {
                sql = sql.substring(0, end);
            }
        }
        log.debug("ParseByDB sql: {}", sql);

        try {
            SQLSelectQueryBlock query = (SQLSelectQueryBlock) parseSQLSelectQuery(sql, dbType);
            List<SQLSelectItem> selectList = query.getSelectList();
            Stream<String> result = selectList.stream().map(si -> si.getAlias() != null ? si.getAlias() : si.toString());
            return new Result(Result.SUCCESS, "", result);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return new Result(Result.ERROR, e.getMessage());
        }
    }

    private SQLSelectQuery parseSQLSelectQuery(String sql, String dbProductName) {
        sql = parseSqlCondition(sql);
        log.debug("dbProductName:" + dbProductName);

        DbType dbType = null;
        if (dbProductName.toLowerCase().equals("oracle")) {
            dbType = DbType.oracle;
        } else if (dbProductName.toLowerCase().equals("sql")) {
            dbType = DbType.sqlserver;
        } else if (dbProductName.toLowerCase().equals("mysql")) {
            dbType = DbType.mysql;
        } else if (dbProductName.toLowerCase().equals("pgsql")) {
            dbType = DbType.postgresql;
        } else if (dbProductName.toLowerCase().equals("oceanbase")) {
            dbType = DbType.oceanbase;
        } else if (dbProductName.toLowerCase().equals("dm")) {
            dbType = DbType.dm;
        }

        SQLStatement sqlStatement = SQLUtils.parseSingleStatement(sql, dbType, false);
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) sqlStatement;
        SQLSelect select = sqlSelectStatement.getSelect();
        return select.getQuery();
    }

    public String parseSqlCondition(String source) {
        try {
            source = source.replaceAll("(?i)\\[epidtb]", "").replaceAll("(?i)\\[epid]", "").replaceAll("(?i)\\[staffid]", "").replaceAll("(?i)\\[rolegroupid]", "");
            source = source.replaceAll("(?i)\\{C_EPIDTB}", "").replaceAll("(?i)\\{C_EPID}", "").replaceAll("(?i)\\{C_GRP}", "").replaceAll("(?i)\\{C_BY}", "").replaceAll("(?i)\\{C_NOW}", "").replaceAll("(?i)\\{C_LANG}", "").replaceAll("(?i)\\{C_GUID}", "").replaceAll("(?i)\\{C_SFID}", "");

            source = source.replaceAll("(?s)%B%.*?%E%", "");

            Pattern p = Pattern.compile("!\\((\\w+)\\)");
            Matcher matcher = p.matcher(source);
            while (matcher.find()) {
                int gc = matcher.groupCount();
                if (gc >= 1) {
                    String exp = matcher.group(1);
                    source = source.replaceAll("!\\(" + exp + "\\)", exp);
                }
            }
            Pattern pattern = Pattern.compile("#\\((.*?)\\)");
            Matcher match = pattern.matcher(source);
            while (match.find()) {
                int groupCount = match.groupCount();
                if (groupCount >= 1) {
                    String exp = match.group(1);
                    source = source.replaceAll("#\\(" + exp + "\\)", "");
                }
            }
        } catch (Exception e) {
            log.error("", e);
        }
        return source;
    }

    /**
     * 使用EasyExcel异步导出
     *
     * @param exportInfo
     * @return
     * @throws Exception
     */
    @PostMapping("/dynPageDataAsyncExport")
    @ResponseBody
    public Result dynPageDataAsyncExport(@RequestBody DataExportRequest exportInfo) throws Exception {
        HttpSession httpSession = request.getSession();
        Staff staff = (Staff) httpSession.getAttribute(Constant.STRING_STAFF);
        ProjInfo project = StaffUtils.getCurProject(staff);
        String token = project.getToken();
        int digest = project.getOrdIndex();
        if (!allowExport(token)) {
            return new Result(Result.ERROR, i18n("export.limit"));
        }
        DbSqlInfo sqlInfo = exportInfo.getSqlInfo();
        String dynId = sqlInfo.getDynId();
        String dbPool = StringUtils.isBlank(sqlInfo.getDbPool()) ? "" : sqlInfo.getDbPool();
        // check repeat task
        String jobId = RandomUtils.getGUID();
        Result result = sqlQuery(token, digest, dbPool, "System.asyncjob.running.check", 1, 0, dynId);
        DSBean dsb = getExecuteData(result);
        List<List<Object>> rows = dsb.getFields();
        String filePath = config.getExportDownloadPath() + jobId + FILE_SUFFIX;
        String fileName = exportInfo.getFileName() + "_" + DateUtils.format(new Date(), "yyyyMMddHHmmss") + FILE_SUFFIX;
        if (rows.size() > 0 && (int) rows.get(0).get(0) == 0) {
            result = sqlExecute(token, digest, dbPool, "System.asyncjob.insert", jobId, "dyn.reporter.run", "", "", filePath, fileName, dynId);
            if (result.getCode() < 0) {
                return new Result(Result.ERROR, i18n("export.save.fail"));
            }
            String version = sqlInfo.getVersion();
            String sqlKey;
            if (StringUtils.isBlank(version)) {
                sqlKey = "VE.dyn.sqls.select";
            } else {
                sqlKey = "VE.dyn.sqls.publish.select";
            }
            Result sqlResult = sqlQuery(token, digest, dbPool, sqlKey, 0, 0, dynId, version);
            if (sqlResult.getCode() < 0) {
                return new Result(Result.ERROR, i18n("export.query.fail"));
            }
            DSBean dsBean = getExecuteData(sqlResult);
            if (dsBean.getCount() > 0) {
                List<List<Object>> sqlRows = dsBean.getFields();
                String dynSql = "";
                for (List<Object> row : sqlRows) {
                    dynSql = Objects.toString(dsBean.getField(row, "CONTENT"));
                }
                log.debug("dynPageDataAsyncExport开始执行动态页面数据导出 ---> jobId：{}, staff：{}, dynSql：{}", jobId, staff, dynSql);
                tableDataExportService.dynPageAsyncExportTask(staff, token, dynSql, jobId, exportInfo);
                return new Result(Result.SUCCESS, i18n("export.running"));
            } else {
                return new Result(Result.ERROR, i18n("dynPage.sql.not.exist", dynId, version));
            }
        } else {
            return new Result(Result.ERROR, i18n("export.repeat"));
        }
    }

    public synchronized boolean allowExport(String token) throws JssException {
        DataSource dataSource = JssTools.getJdbc(token);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String checkTaskSql = "SELECT COUNT(1) AS num FROM job_async WHERE MAINKEY = 'dyn.reporter.run' and STATUS = 1";
        Map<String, Object> resultMap = jdbcTemplate.queryForMap(checkTaskSql);
        int num = Integer.parseInt(String.valueOf(resultMap.get("num")));
        if (num < config.getMaxExportTasks()) {
            return true;
        }
        return false;
    }

    @GetMapping("/{jobId}/download")
    @ResponseBody
    public Result downloadFile(@PathVariable(name = "jobId") String jobId) throws Exception {
        HttpSession httpSession = request.getSession();
        Staff staff = (Staff) httpSession.getAttribute(Constant.STRING_STAFF);
        ProjInfo project = StaffUtils.getCurProject(staff);
        String token = project.getToken();
        DataSource dataSource = JssTools.getJdbc(token);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String queryFileSql = "SELECT * FROM job_async WHERE JOBID = ?";
        try {
            Map<String, Object> resultMap = jdbcTemplate.queryForMap(queryFileSql, jobId);
            File file = new File(String.valueOf(resultMap.get("DOCPATH")));
            if (file.exists()) {
                try (OutputStream os = new BufferedOutputStream(response.getOutputStream())) {
                    String filename = String.valueOf(resultMap.get("DOCNAME"));
                    if (request.getHeader("User-Agent").toLowerCase().contains("firefox")) {
                        response.addHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(filename, "UTF-8"));
                    } else {
                        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
                    }
                    response.setContentType("application/msexcel;charset=UTF-8");
                    os.write(FileUtils.readFileToByteArray(file));
                    os.flush();
                } catch (Exception e) {
                    log.error("下载异常：{}", e.getMessage(), e);
                }
            } else {
                return new Result(Result.ERROR, "下载文件失败，文件不存在");
            }
        } catch (Exception e) {
            log.error("任务查询异常：{}", e.getMessage(), e);
            return new Result(Result.ERROR, "下载文件失败，文件或任务不存在");
        }
        return null;
    }
}
